package cn.jly.spring.boot.cloud.dao;

import cn.jly.spring.boot.cloud.beans.User;
import cn.jly.spring.boot.cloud.dto.UserDto;
import cn.jly.spring.boot.cloud.dto.UserExtendDto;
import cn.jly.spring.boot.cloud.dto.UserExtendDtoInterface;
import cn.jly.spring.boot.cloud.dto.UserOnlyNameEmail;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author lanyangji
 * @description
 * @date 2020/11/25 下午 3:30
 */
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    /**
     * 构造查询方法的时候也可以传入分页或者排序参数
     *
     * @param email
     * @param sort
     * @return
     */
    List<User> getUsersByEmailLike(String email, Sort sort);

    /**
     * List<User> getUsersByEmailLike(String email, Pageable pageable);
     *
     * @param email
     * @param pageable
     * @return
     */
    Page<User> getUsersByEmailLike(String email, Pageable pageable);

    List<User> getUsersByEmailAndUsernameAllIgnoreCase(String email, String username);

    List<User> deleteAllByUsernameContains(String username);

    List<User> getDistinctTop3ByUsernameLike(String username);

    List<User> getFirst3ByUsernameLike(String username);

    /**
     * repository支持返回具体的dto，从而返回自己需要的字段
     * 注意dto只能有一个全参构造器
     *
     * @param email
     * @return
     */
    UserDto getUserByEmailLike(String email);

    /**
     * 用pojo接口形式接受spring data jpa的返回值
     *
     * @param email
     * @return
     */
    UserOnlyNameEmail getUserByEmailContains(String email);

    @Query("from User where username = :username")
    User getByUsername(String username);

    @Query("select u from User u")
    List<User> getByCondition(Pageable pageable);

    @Query("from User u where u.username like %:username")
    List<User> getByUsernameEndWith(@Param("username") String username, Pageable pageable);

    @Query(value = "select * from tbl_user where email like concat('%', ?1, '%')",
            countQuery = "select count(*) from tbl_user where email like concat('%', ?1, '%')",
            nativeQuery = true)
    Page<User> getUsersByEmailLikeNativeQuery(String email, Pageable pageable);

    @Query("select new cn.jly.spring.boot.cloud.dto.UserExtendDto(u.username, u.email, d.deptName) from User u, Department d where u.id = :id and d.id = :id")
    UserExtendDto getById(@Param("id") long id);

    @Query("select u.email as email, u.username as username, d.deptName as deptName" +
            " from User u, Department d where u.id = :id and d.id = :id")
    UserExtendDtoInterface getByIdAndReturnDtoInterface(@Param("id") long id);

    /**
     * JPQL实现动态查询
     *
     * @param username
     * @param email
     * @return
     */
    @Query("select new cn.jly.spring.boot.cloud.dto.UserDto(u.username, u.email) from User u where (:username is null or u.username = :username) " +
            "and (:email is null or u.email = :email)")
    UserDto getByUser(@Param("username") String username, @Param("email") String email);

    /**
     * 本地sql实现动态查询
     *
     * @param user
     * @return
     */
    @Query(nativeQuery = true,
            value = "select u.user_name as username, u.email from tbl_user u where " +
                    "(:#{#user.username} is null or u.user_name = :#{#user.username}) " +
                    "and (:#{#user.email} is null or u.email = :#{#user.email})")
    UserOnlyNameEmail getByUser(@Param("user") User user);
}
